﻿// --------------------------------------------------------------------------------------
// <copyright file="AdministrativeDAO.cs" company="NGroup">
// Copyright (c) NGroup. All rights reserved.
// </copyright>
// --------------------------------------------------------------------------------------
// <history>
//     <change who="Bui Thanh Nam" date="2013.12.15">Create</change>
// </history>
// --------------------------------------------------------------------------------------

namespace NGroup.VTL.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using NGroup.VTL.Core.Const;
    using NGroup.VTL.Core.Utilities;
    using NGroup.VTL.DAL.Abstraction;
    using NGroup.VTL.DAL.Extension;
    using NGroup.VTL.Models;
    using NGroup.VTL.WebCore;

    public class AdministrativeDAO : IAdministrativeDAO
    {
        public bool AddNewProduct(ProductModel model)
        {
            string spName = "AddNewProduct";
            object newIdentity = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("SupTypeID", model.SupTypeID);
                command.AddInParameter("TypeID", model.TypeID);
                command.AddInParameter("UniqueString", model.UniqueString);
                command.AddInParameter("HomePageDisplayBlockID", Convert.ToInt32(model.HomePageDisplayBlockID));
                command.AddInParameter("IsActive", model.IsActive);
                command.AddInParameter("Title", model.Title);
                command.AddInParameter("MetaContent", model.MetaContent);
                command.AddInParameter("MetaDescription", model.MetaDescription);
                command.AddInParameter("Content", model.Content);
                command.AddInParameter("ProductDetail", CommonFunction.fixNullString(model.ProductDetail));
                command.AddInParameter("DeliveryDetail", CommonFunction.fixNullString(model.DeliveryDetail));
                command.AddInParameter("ImageSmallURL", CommonFunction.fixNullString(model.ImageSmallURL));
                command.AddInParameter("ImageLargeURL", model.ImageLargeURL);

                command.AddInParameter("TitleEn", model.TitleEn);
                command.AddInParameter("MetaContentEn", model.MetaContentEn);
                command.AddInParameter("MetaDescriptionEn", model.MetaDescriptionEn);
                command.AddInParameter("ContentEn", model.ContentEn);
                command.AddInParameter("ProductDetailEn", CommonFunction.fixNullString(model.ProductDetailEn));
                command.AddInParameter("ImageSmallURLEn", CommonFunction.fixNullString(model.ImageSmallURLEn));
                command.AddInParameter("ImageLargeURLEn", model.ImageLargeURLEn);

                command.AddInParameter("CreatedAt", model.CreatedAt);
                command.AddInParameter("CreatedBy", model.CreatedBy);
                command.AddInParameter("Price", model.Price);
                command.AddInParameter("PriceEn", model.PriceEn);

                command.AddOutParameter("NewIdentity", -1);
                command.ExecuteNonQuery();

                newIdentity = command.GetParameterValue("NewIdentity");
            }

            long newIdentityValue;

            if (long.TryParse(newIdentity.ToString(), out newIdentityValue) && newIdentityValue != -1)
            {
                return true;
            }

            return false;
        }


        public bool AddNewNews(NewsModel model)
        {
            //if (
            //    model.TypeID == GenericTypes.SiteMap
            //    || model.TypeID == GenericTypes.HuongDanThanhToan
            //    || model.TypeID == GenericTypes.LienHe
            //    || model.TypeID == GenericTypes.KhachHang)
            //{
            //    model.NewsID = -9; //hard code ID
            //    UpdateNews(model);
            //    return true;
            //}
            /**
             * don't need to take care about this because we hard code url in menu, so just only focus on url
             */

            string spName = "AddNewNews";
            object newIdentity = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("TypeID", Convert.ToInt32(model.TypeID));
                command.AddInParameter("UniqueString", model.UniqueString);
                command.AddInParameter("MetaContent", model.MetaContent);
                command.AddInParameter("MetaContentEn", model.MetaContent);
                command.AddInParameter("MetaDescription", model.MetaDescription);
                command.AddInParameter("MetaDescriptionEn", model.MetaDescriptionEn);
                command.AddInParameter("IsActive", model.IsActive);

                command.AddInParameter("Title", model.Title);
                command.AddInParameter("TitleEn", model.TitleEn);
                //command.AddInParameter("BriefContent", model.BriefContent);
                command.Parameters.Add("@BriefContent", SqlDbType.NVarChar, 4000).Value = model.BriefContent;
                command.Parameters.Add("@BriefContentEn", SqlDbType.NVarChar, 4000).Value = model.BriefContentEn;
                command.AddInParameter("FullContent", model.FullContent);
                command.AddInParameter("ImageSmallURL", model.ImageSmallURL);

                command.AddInParameter("FullContentEn", model.FullContentEn);
                command.AddInParameter("ImageSmallURLEn", model.ImageSmallURLEn);

                command.AddInParameter("CreatedAt", model.CreatedAt);
                command.AddInParameter("CreatedBy", model.CreatedBy);

                command.AddOutParameter("NewIdentity", -1);
                command.ExecuteNonQuery();

                newIdentity = command.GetParameterValue("NewIdentity");
            }

            long newIdentityValue;

            if (long.TryParse(newIdentity.ToString(), out newIdentityValue) && newIdentityValue != -1)
            {
                return true;
            }

            return false;
        }

        public ProductModel GetProductDetailForEdit(int parentTypeID, int childTypeID, string uniqueString)
        {
            string spName = "GetProductDetailForEdit";
            DataSet resultDS = null;
            var product = new ProductModel();

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("UniqueString", uniqueString);
                command.AddInParameter("ParentTypeID", parentTypeID);
                command.AddInParameter("ChildTypeID", childTypeID);

                resultDS = command.ExecuteDataset();
            }

            if (resultDS != null && resultDS.Tables.Count > 0 && resultDS.Tables[0].Rows.Count > 0)
            {
                DataRow result = resultDS.Tables[0].Rows[0];
                product = ConvertDataTableToProductModel(result);
            }

            return product;
        }

        public void UpdateProduct(ProductModel model)
        {
            string spName = "UpdateProduct";

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("ID", Convert.ToInt32(model.ProductID));
                command.AddInParameter("TypeID", Convert.ToInt32(model.TypeID));
                command.AddInParameter("UniqueString", model.UniqueString);
                command.AddInParameter("HomePageDisplayBlockID", Convert.ToInt32(model.HomePageDisplayBlockID));
                command.AddInParameter("MetaContent", model.MetaContent);
                command.AddInParameter("MetaContent_En", model.MetaContent);
                command.AddInParameter("MetaDescription", model.MetaDescription);
                command.AddInParameter("MetaDescription_En", model.MetaDescriptionEn);
                command.AddInParameter("IsActive", model.IsActive);
                command.AddInParameter("Title", model.Title);
                command.AddInParameter("Content", model.Content);
                command.AddInParameter("ProductDetail", CommonFunction.fixNullString(model.ProductDetail));
                command.AddInParameter("DeliveryDetail", CommonFunction.fixNullString(model.DeliveryDetail));
                command.AddInParameter("ImageSmallURL", CommonFunction.fixNullString(model.ImageSmallURL));
                command.AddInParameter("ImageLargeURL", model.ImageLargeURL);

                command.AddInParameter("Title_En", model.TitleEn);
                command.AddInParameter("Content_En", model.ContentEn);
                command.AddInParameter("ProductDetail_En", CommonFunction.fixNullString(model.ProductDetailEn));
                command.AddInParameter("ImageSmallURL_En", CommonFunction.fixNullString(model.ImageSmallURLEn));
                command.AddInParameter("ImageLargeURL_En", model.ImageLargeURLEn);

                command.AddInParameter("LastUpdatedAt", model.LastUpdatedAt);
                command.AddInParameter("LastUpdatedBy", model.LastUpdatedBy);
                command.AddInParameter("Price", model.Price);
                command.AddInParameter("Price_En", model.PriceEn);

                command.ExecuteNonQuery();
            }
        }

        public void UpdateNews(NewsModel model)
        {
            string spName = "UpdateNews";

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;
                command.AddInParameter("ID", Convert.ToInt32(model.NewsID));
                command.AddInParameter("TypeID", Convert.ToInt32(model.TypeID));
                command.AddInParameter("UniqueString", model.UniqueString);
                command.AddInParameter("MetaContent", model.MetaContent);
                command.AddInParameter("MetaContent_En", model.MetaContentEn);
                command.AddInParameter("MetaDescription", model.MetaDescription);
                command.AddInParameter("MetaDescription_En", model.MetaDescriptionEn);
                command.AddInParameter("IsActive", model.IsActive);
                command.AddInParameter("Title", model.Title);
                command.AddInParameter("Title_En", model.TitleEn);
                //command.AddInParameter("BriefContent", model.BriefContent);
                command.Parameters.Add("@BriefContent", SqlDbType.NVarChar, 4000).Value = model.BriefContent;
                command.Parameters.Add("@BriefContent_En", SqlDbType.NVarChar, 4000).Value = model.BriefContentEn;
                command.AddInParameter("FullContent", model.FullContent);
                command.AddInParameter("ImageSmallURL", model.ImageSmallURL);
                command.AddInParameter("FullContent_En", model.FullContentEn);
                command.AddInParameter("ImageSmallURL_En", model.ImageSmallURLEn);
                command.AddInParameter("LastUpdatedAt", model.LastUpdatedAt);
                command.AddInParameter("LastUpdatedBy", model.LastUpdatedBy);

                command.ExecuteNonQuery();
            }
        }

        public UserModel GetUserInformation(string username, string password)
        {
            string spName = "GetUserInformation";
            DataSet resultDS = null;
            UserModel userInformation = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("Username", username);
                command.AddInParameter("Password", password);

                resultDS = command.ExecuteDataset();
            }

            if (resultDS != null && resultDS.Tables.Count > 0)
            {
                DataTable resultTable = resultDS.Tables[0];

                userInformation = this.ConvertDataTableToUserModel(resultTable);
            }

            return userInformation;
        }

        public List<UserModel> GetUserListByPagingInfo(int currentUserRoleId, ref PagingInfoModel pagingInfo)
        {
            string spName = "GetUserList";
            DataSet resultDS = null;
            object totalRows;
            List<UserModel> userList = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("PageIndex", pagingInfo.CurrentPage);
                command.AddInParameter("PageSize", pagingInfo.PageSize);
                command.AddInParameter("Permission", currentUserRoleId);
                command.AddOutParameter("TotalRows", 0);

                resultDS = command.ExecuteDataset();
                totalRows = command.GetParameterValue("TotalRows");
            }

            if (resultDS != null && resultDS.Tables.Count > 0)
            {
                int totalRowsValue;

                if (int.TryParse(totalRows.ToString(), out totalRowsValue))
                {
                    pagingInfo.UpdateValues(totalRowsValue);
                }

                DataTable resultTable = resultDS.Tables[0];

                userList = this.ConvertDataTableToUserModelList(resultTable);
            }

            return userList;
        }

        public bool AddNewUser(UserModel user)
        {
            string spName = "AddNewUser";
            object newIdentity = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("RoleID", user.RoleID);
                command.AddInParameter("Username", user.Username);
                command.AddInParameter("Password", user.Password);
                command.AddInParameter("UserAlias", user.UserAlias);
                command.AddInParameter("IsActive", user.IsActive);
                command.AddInParameter("CreatedAt", user.CreatedAt);
                command.AddInParameter("CreatedBy", user.CreatedBy);
                command.AddInParameter("LastUpdatedAt", user.LastUpdatedAt);
                command.AddInParameter("LastUpdatedBy", user.LastUpdatedBy);

                command.AddOutParameter("NewIdentity", -1);
                command.ExecuteNonQuery();

                newIdentity = command.GetParameterValue("NewIdentity");
            }

            long newIdentityValue;

            if (long.TryParse(newIdentity.ToString(), out newIdentityValue) && newIdentityValue != -1)
            {
                return true;
            }

            return false;
        }

        public List<ProductModel> GetProductListByPagingInfo(string filterAuthor, string filterProductSupType, string filterProductType, ref PagingInfoModel pagingInfo)
        {
            string spName = "GetProductListForAdministration";
            DataSet resultDS = null;
            object totalRows;
            List<ProductModel> producList = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("PageIndex", pagingInfo.CurrentPage);
                command.AddInParameter("PageSize", pagingInfo.PageSize);
                command.AddInParameter("Author", filterAuthor);
                command.AddInParameter("ProductSupTypeID", filterProductSupType);
                command.AddInParameter("ProductTypeID", filterProductType);

                command.AddOutParameter("TotalRows", 0);

                resultDS = command.ExecuteDataset();
                totalRows = command.GetParameterValue("TotalRows");
            }

            if (resultDS != null && resultDS.Tables.Count > 0)
            {
                int totalRowsValue;

                if (int.TryParse(totalRows.ToString(), out totalRowsValue))
                {
                    pagingInfo.UpdateValues(totalRowsValue);
                }

                DataTable resultTable = resultDS.Tables[0];

                producList = this.ConvertDataTableToProductModelList(resultTable);
            }

            return producList;
        }

        public List<NewsModel> GetNewsListByPagingInfo(string filterAuthor, int filterNewsTypeId, ref PagingInfoModel pagingInfo)
        {
            string spName = "GetNewsListForAdministration";
            DataSet resultDS = null;
            object totalRows;
            List<NewsModel> newsList = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("PageIndex", pagingInfo.CurrentPage);
                command.AddInParameter("PageSize", pagingInfo.PageSize);
                command.AddInParameter("Author", filterAuthor);
                command.AddInParameter("NewsTypeID", filterNewsTypeId);

                command.AddOutParameter("TotalRows", 0);

                resultDS = command.ExecuteDataset();
                totalRows = command.GetParameterValue("TotalRows");
            }

            if (resultDS != null && resultDS.Tables.Count > 0)
            {
                int totalRowsValue;

                if (int.TryParse(totalRows.ToString(), out totalRowsValue))
                {
                    pagingInfo.UpdateValues(totalRowsValue);
                }

                DataTable resultTable = resultDS.Tables[0];

                newsList = this.ConvertDataTableToNewsModelList(resultTable);
            }

            return newsList;
        }

        public List<OrderModel> GetOrderListByPagingInfo(string filterProductSupType, string filterProductType, ref PagingInfoModel pagingInfo)
        {
            string spName = "GetActiveOrderList";
            DataSet resultDS = null;
            object totalRows;
            List<OrderModel> orderList = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("PageIndex", pagingInfo.CurrentPage);
                command.AddInParameter("PageSize", pagingInfo.PageSize);
                command.AddInParameter("ProductTypeID", filterProductType);
                command.AddInParameter("ProductSupTypeID", filterProductSupType);

                command.AddOutParameter("TotalRows", 0);

                resultDS = command.ExecuteDataset();
                totalRows = command.GetParameterValue("TotalRows");
            }

            if (resultDS != null && resultDS.Tables.Count > 0)
            {
                int totalRowsValue;

                if (int.TryParse(totalRows.ToString(), out totalRowsValue))
                {
                    pagingInfo.UpdateValues(totalRowsValue);
                }

                DataTable resultTable = resultDS.Tables[0];

                orderList = this.ConvertDataTableToOrderModelList(resultTable);
            }

            return orderList;
        }

        public bool CheckUniqueString(int typeID, string uniqueString)
        {
            string spName = "CheckUniqueString";
            object isExisting = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("TypeID", typeID);
                command.AddInParameter("UniqueString", uniqueString);

                command.AddOutParameter("IsExisting", 1);
                command.ExecuteNonQuery();

                isExisting = command.GetParameterValue("IsExisting");
            }

            int isExistingValue;

            if (int.TryParse(isExisting.ToString(), out isExistingValue))
            {
                return isExistingValue == 1;
            }

            return false;
        }

        public List<GenericTypeItem> GetAllContentTypesConfigurations()
        {
            string spName = "GetAllGenericTypes";
            DataSet resultDS = null;

            List<GenericTypeItem> genericTypeItemList = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;
                resultDS = command.ExecuteDataset();
            }

            if (resultDS != null && resultDS.Tables.Count > 0)
            {
                DataTable resultTable = resultDS.Tables[0];

                genericTypeItemList = this.ConverDataTableToGenericTypeItemList(resultTable);
            }

            return genericTypeItemList;
        }

        public int GetPendingOrdersCount()
        {
            string spName = "GetPendingOrdersCount";
            object count = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddOutParameter("Count", -1);
                command.ExecuteNonQuery();

                count = command.GetParameterValue("Count");
            }

            int countValue;

            if (int.TryParse(count.ToString(), out countValue) && countValue != -1)
            {
                return countValue;
            }

            return 0;
        }

        private List<OrderModel> ConvertDataTableToOrderModelList(DataTable data)
        {
            List<OrderModel> orderList = null;

            if (data != null)
            {
                orderList = new List<OrderModel>();

                foreach (DataRow dr in data.Rows)
                {
                    OrderModel model = new OrderModel();

                    model.ID = SqlUtil<int>.GetValue(dr, "ID");
                    model.ProductID = SqlUtil<int>.GetValue(dr, "ProductID");
                    model.CustomerName = SqlUtil<string>.GetValue(dr, "CustomerName");
                    model.CustomerPhone = SqlUtil<string>.GetValue(dr, "CustomerPhone");
                    model.ShippingAddress = SqlUtil<string>.GetValue(dr, "ShippingAddress");
                    model.OrderDate = SqlUtil<DateTime>.GetValue(dr, "OrderDate");
                    model.Amount = SqlUtil<int>.GetValue(dr, "Amount");
                    model.TotalPrice = SqlUtil<decimal>.GetValue(dr, "TotalPrice");
                    model.IsActive = SqlUtil<bool>.GetValue(dr, "IsActive");
                    model.ProductSupTypeID = SqlUtil<int>.GetValue(dr, "ProductSupTypeID");
                    model.ProductTypeID = SqlUtil<int>.GetValue(dr, "ProductTypeID");
                    model.UniqueString = SqlUtil<string>.GetValue(dr, "UniqueString");

                    orderList.Add(model);
                }
            }

            return orderList;
        }

        private List<GenericTypeItem> ConverDataTableToGenericTypeItemList(DataTable data)
        {
            List<GenericTypeItem> genericTypeItemList = null;

            if (data != null)
            {
                genericTypeItemList = new List<GenericTypeItem>();

                foreach (DataRow dr in data.Rows)
                {
                    GenericTypeItem model = new GenericTypeItem();

                    model.CanEdit = SqlUtil<bool>.GetValue(dr, "CanEdit");
                    model.CreatedAt = SqlUtil<DateTime>.GetValue(dr, "CreatedAt");
                    model.CreatedBy = SqlUtil<string>.GetValue(dr, "CreatedBy");
                    model.Description = SqlUtil<string>.GetValue(dr, "Description");
                    model.IsActive = SqlUtil<bool>.GetValue(dr, "IsActive");
                    model.LastUpdatedAt = SqlUtil<DateTime>.GetValue(dr, "LastUpdatedAt");
                    model.LastUpdatedBy = SqlUtil<string>.GetValue(dr, "LastUpdatedBy");
                    model.SupTypeID = SqlUtil<int>.GetValue(dr, "SupTypeID");
                    model.TypeID = SqlUtil<int>.GetValue(dr, "TypeID");
                    model.UniqueString = SqlUtil<string>.GetValue(dr, "UniqueString");
                    model.MetaDescription = SqlUtil<string>.GetValue(dr, "MetaDescription");
                    model.MetaDescription_En = SqlUtil<string>.GetValue(dr, "MetaDescription_En");
                    model.MetaKeyword = SqlUtil<string>.GetValue(dr, "MetaKeyword");
                    model.MetaKeyword_En = SqlUtil<string>.GetValue(dr, "MetaKeyword_En");

                    genericTypeItemList.Add(model);
                }
            }

            return genericTypeItemList;
        }

        private List<NewsModel> ConvertDataTableToNewsModelList(DataTable data)
        {
            List<NewsModel> newsList = null;

            if (data != null)
            {
                newsList = new List<NewsModel>();

                foreach (DataRow dr in data.Rows)
                {
                    NewsModel model = new NewsModel();

                    model.NewsID = SqlUtil<int>.GetValue(dr, "ID");
                    model.UniqueString = SqlUtil<string>.GetValue(dr, "UniqueString");
                    model.Title = SqlUtil<string>.GetValue(dr, "Title");
                    model.CreatedAt = SqlUtil<DateTime>.GetValue(dr, "CreatedAt");
                    model.CreatedBy = SqlUtil<string>.GetValue(dr, "CreatedBy");
                    model.TypeID = SqlUtil<int>.GetValue(dr, "TypeID");

                    newsList.Add(model);
                }
            }

            return newsList;
        }

        private List<ProductModel> ConvertDataTableToProductModelList(DataTable data)
        {
            List<ProductModel> productList = null;

            if (data != null)
            {
                productList = new List<ProductModel>();

                foreach (DataRow dr in data.Rows)
                {
                    ProductModel model = new ProductModel();

                    model.ProductID = SqlUtil<int>.GetValue(dr, "ID");
                    model.UniqueString = SqlUtil<string>.GetValue(dr, "UniqueString");
                    model.Title = SqlUtil<string>.GetValue(dr, "Title");
                    model.CreatedAt = SqlUtil<DateTime>.GetValue(dr, "CreatedAt");
                    model.CreatedBy = SqlUtil<string>.GetValue(dr, "CreatedBy");
                    model.TypeID = SqlUtil<int>.GetValue(dr, "TypeID");
                    model.SupTypeID = SqlUtil<int>.GetValue(dr, "SupTypeID");

                    productList.Add(model);
                }
            }

            return productList;
        }

        private List<UserModel> ConvertDataTableToUserModelList(DataTable data)
        {
            List<UserModel> usermodel = null;

            if (data != null)
            {
                usermodel = new List<UserModel>();

                foreach (DataRow dr in data.Rows)
                {
                    UserModel model = new UserModel();

                    model.UserID = SqlUtil<int>.GetValue(dr, "ID");
                    model.RoleID = SqlUtil<int>.GetValue(dr, "RoleID");
                    model.Username = SqlUtil<string>.GetValue(dr, "Username");
                    model.Password = SqlUtil<string>.GetValue(dr, "Password");
                    model.IsActive = SqlUtil<bool>.GetValue(dr, "IsActive");
                    model.CreatedAt = SqlUtil<DateTime>.GetValue(dr, "CreatedAt");
                    model.CreatedBy = SqlUtil<string>.GetValue(dr, "CreatedBy");
                    model.LastUpdatedAt = SqlUtil<DateTime>.GetValue(dr, "LastUpdatedAt");
                    model.LastUpdatedBy = SqlUtil<string>.GetValue(dr, "LastUpdatedBy");

                    usermodel.Add(model);
                }
            }

            return usermodel;
        }

        private UserModel ConvertDataTableToUserModel(DataTable data)
        {
            UserModel user = null;

            if (data != null && data.Rows.Count == 1)
            {
                DataRow dr = data.Rows[0];
                user = new UserModel();

                user.CreatedAt = SqlUtil<DateTime>.GetValue(dr, "CreatedAt");
                user.CreatedBy = SqlUtil<string>.GetValue(dr, "CreatedBy");
                user.IsActive = SqlUtil<bool>.GetValue(dr, "IsActive");
                user.LastUpdatedAt = SqlUtil<DateTime>.GetValue(dr, "LastUpdatedAt");
                user.LastUpdatedBy = SqlUtil<string>.GetValue(dr, "LastUpdatedBy");
                user.Password = SqlUtil<string>.GetValue(dr, "Password");
                user.RoleID = SqlUtil<int>.GetValue(dr, "RoleID");
                user.UserAlias = SqlUtil<string>.GetValue(dr, "UserAlias");
                user.Username = SqlUtil<string>.GetValue(dr, "Username");
                user.UserID = SqlUtil<int>.GetValue(dr, "ID");
            }

            return user;
        }


        public bool DeleteProduct(ProductModel model)
        {
            string spName = "DeleteProduct";
            object Rowcount = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("ID", Convert.ToInt32(model.ProductID));
                command.AddInParameter("IsActive", model.IsActive);
                command.AddInParameter("LastUpdatedAt", model.LastUpdatedAt);
                command.AddInParameter("LastUpdatedBy", model.LastUpdatedBy);

                command.AddOutParameter("Rowcount", -1);
                command.ExecuteNonQuery();

                Rowcount = command.GetParameterValue("Rowcount");
            }
            long RowcountValue;
            if (long.TryParse(Rowcount.ToString(), out RowcountValue) && RowcountValue == 1)
            {
                return true;
            }

            return false;
        }


        public bool DeleteNews(NewsModel model)
        {
            string spName = "DeleteNews";
            object Rowcount = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("ID", Convert.ToInt32(model.NewsID));
                command.AddInParameter("IsActive", model.IsActive);
                command.AddInParameter("LastUpdatedAt", model.LastUpdatedAt);
                command.AddInParameter("LastUpdatedBy", model.LastUpdatedBy);

                command.AddOutParameter("Rowcount", -1);
                command.ExecuteNonQuery();

                Rowcount = command.GetParameterValue("Rowcount");
            }
            long RowcountValue;
            if (long.TryParse(Rowcount.ToString(), out RowcountValue) && RowcountValue != -1)
            {
                return true;
            }

            return false;
        }

        public bool DeleteUser(UserModel model)
        {
            string spName = "DeleteUser";
            object Rowcount = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("ID", Convert.ToInt32(model.UserID));
                command.AddInParameter("IsActive", model.IsActive);
                command.AddInParameter("LastUpdatedAt", model.LastUpdatedAt);
                command.AddInParameter("LastUpdatedBy", model.LastUpdatedBy);

                command.AddOutParameter("Rowcount", -1);
                command.ExecuteNonQuery();

                Rowcount = command.GetParameterValue("Rowcount");
            }
            long RowcountValue;
            if (long.TryParse(Rowcount.ToString(), out RowcountValue) && RowcountValue != -1)
            {
                return true;
            }

            return false;
        }

        public bool DeleteOrder(int orderId)
        {
            string spName = "DeleteOrder";
            object Rowcount = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("OrderId", Convert.ToInt32(orderId));

                command.AddOutParameter("Rowcount", -1);
                command.ExecuteNonQuery();

                Rowcount = command.GetParameterValue("Rowcount");
            }

            long RowcountValue;
            if (long.TryParse(Rowcount.ToString(), out RowcountValue) && RowcountValue != -1)
            {
                return true;
            }

            return false;
        }


        public bool UpdateUser(UserModel model)
        {
            string spName = "UpdateUser";
            object Rowcount = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("Username", model.Username);
                command.AddInParameter("Password", model.Password);
                command.AddInParameter("UserAlias", model.UserAlias);
                command.AddInParameter("LastUpdatedAt", model.LastUpdatedAt);
                command.AddInParameter("LastUpdatedBy", model.LastUpdatedBy);

                command.AddOutParameter("Rowcount", -1);
                command.ExecuteNonQuery();

                Rowcount = command.GetParameterValue("Rowcount");
            }

            long RowcountValue;
            if (long.TryParse(Rowcount.ToString(), out RowcountValue) && RowcountValue != -1)
            {
                return true;
            }

            return false;
        }


        public bool DeleteGenericType(GenericTypeItem model)
        {
            string spName = "DeleteGenericType";
            object Rowcount = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("ID", Convert.ToInt32(model.TypeID));
                command.AddInParameter("IsActive", model.IsActive);
                command.AddInParameter("LastUpdatedAt", model.LastUpdatedAt);
                command.AddInParameter("LastUpdatedBy", model.LastUpdatedBy);

                command.AddOutParameter("Rowcount", -1);
                command.ExecuteNonQuery();

                Rowcount = command.GetParameterValue("Rowcount");
            }
            long RowcountValue;
            if (long.TryParse(Rowcount.ToString(), out RowcountValue) && RowcountValue != -1)
            {
                return true;
            }

            return false;
        }

        public bool UpdateGenericType(GenericTypeItem model)
        {
            string spName = "UpdateGenericType";
            object newIdentity = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;
                command.AddInParameter("ID", Convert.ToInt32(model.TypeID));
                //command.AddInParameter("UniqueString", model.UniqueString);
                command.AddInParameter("Descriptions", model.Description);
                command.AddInParameter("Descriptions_En", model.Description_En);

                command.AddInParameter("MetaDescriptions", model.MetaDescription);
                command.AddInParameter("MetaDescriptions_En", model.MetaDescription_En);
                command.AddInParameter("MetaKeywords", model.MetaKeyword);
                command.AddInParameter("MetaKeywords_En", model.MetaKeyword_En);

                command.AddInParameter("LastUpdatedAt", model.LastUpdatedAt);
                command.AddInParameter("LastUpdatedBy", model.LastUpdatedBy);

                command.AddOutParameter("Rowcount", -1);
                command.ExecuteNonQuery();

                newIdentity = command.GetParameterValue("Rowcount");
            }

            long newIdentityValue;

            if (long.TryParse(newIdentity.ToString(), out newIdentityValue) && newIdentityValue != -1)
            {
                return true;
            }
            return false;
        }

        public bool AddNewGenericType(GenericTypeItem model)
        {
            string spName = "AddNewGenericType";
            object newIdentity = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("UniqueString", model.UniqueString);
                command.AddInParameter("SupTypeID", model.SupTypeID);
                command.AddInParameter("Descriptions", model.Description);
                command.AddInParameter("Descriptions_En", model.Description_En);

                command.AddInParameter("MetaDescriptions", model.MetaDescription);
                command.AddInParameter("MetaDescriptions_En", model.MetaDescription_En);
                command.AddInParameter("MetaKeywords", model.MetaKeyword);
                command.AddInParameter("MetaKeywords_En", model.MetaKeyword_En);

                command.AddInParameter("IsActive", model.IsActive);
                command.AddInParameter("CanEdit", true);
                command.AddInParameter("CreatedAt", model.CreatedAt);
                command.AddInParameter("CreatedBy", model.CreatedBy);

                command.AddOutParameter("NewIdentity", -1);
                command.ExecuteNonQuery();

                newIdentity = command.GetParameterValue("NewIdentity");
            }

            long newIdentityValue;

            if (long.TryParse(newIdentity.ToString(), out newIdentityValue) && newIdentityValue != -1)
            {
                return true;
            }
            return false;
        }


        public bool CheckUniqueStringGT(int? supTypeID, int? typeID, string uniqueString)
        {
            string spName = "CheckUniqueStringGT";
            object isExisting = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("SupTypeID", supTypeID);
                command.AddInParameter("TypeID", typeID);
                command.AddInParameter("UniqueString", uniqueString);

                command.AddOutParameter("IsExisting", 1);
                command.ExecuteNonQuery();

                isExisting = command.GetParameterValue("IsExisting");
            }

            int isExistingValue;

            if (int.TryParse(isExisting.ToString(), out isExistingValue))
            {
                return isExistingValue == 1;
            }

            return false;
        }

        public NewsModel GetNewsDetailForEdit(string uniqueString, LanguageCodes lang)
        {
            string spName = "GetNewsDetailForEdit";
            DataSet resultDS = null;
            var news = new NewsModel();
            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("UniqueString", uniqueString);

                resultDS = command.ExecuteDataset();
            }

            if (resultDS != null && resultDS.Tables.Count > 0 && resultDS.Tables[0].Rows.Count > 0)
            {
                DataRow result = resultDS.Tables[0].Rows[0];
                news = ConvertDataTableToNewsModel(result);
            }

            return news;
        }

        private NewsModel ConvertDataTableToNewsModel(DataRow dr)
        {
            var news = new NewsModel();

            news.NewsID = SqlUtil<int>.GetValue(dr, "ID");
            news.TypeID = SqlUtil<int>.GetValue(dr, "TypeID");
            news.UniqueString = SqlUtil<string>.GetValue(dr, "UniqueString");
            news.Title = SqlUtil<string>.GetValue(dr, "Title");
            news.TitleEn = SqlUtil<string>.GetValue(dr, "Title_En");
            news.IsActive = SqlUtil<bool>.GetValue(dr, "IsActive");
            news.BriefContent = SqlUtil<string>.GetValue(dr, "BriefContent");
            news.BriefContentEn = SqlUtil<string>.GetValue(dr, "BriefContent_En");
            news.FullContent = SqlUtil<string>.GetValue(dr, "FullContent");
            news.FullContentEn = SqlUtil<string>.GetValue(dr, "FullContent_En");
            news.ImageSmallURL = SqlUtil<string>.GetValue(dr, "ImageSmallURL");
            news.ImageSmallURLEn = SqlUtil<string>.GetValue(dr, "ImageSmallURL_En");

            news.CreatedAt = SqlUtil<DateTime>.GetValue(dr, "CreatedAt");
            news.CreatedBy = SqlUtil<string>.GetValue(dr, "CreatedBy");
            news.LastUpdatedAt = SqlUtil<DateTime>.GetValue(dr, "LastUpdatedAt");
            news.LastUpdatedBy = SqlUtil<string>.GetValue(dr, "LastUpdatedBy");
            news.MetaContent = SqlUtil<string>.GetValue(dr, "MetaContent");
            news.MetaContentEn = SqlUtil<string>.GetValue(dr, "MetaContent_En");
            news.MetaDescription = SqlUtil<string>.GetValue(dr, "MetaDescription");
            news.MetaDescriptionEn = SqlUtil<string>.GetValue(dr, "MetaDescription_En");

            return news;
        }

        private ProductModel ConvertDataTableToProductModel(DataRow dr)
        {
            var product = new ProductModel();

            product.ProductID = SqlUtil<int>.GetValue(dr, "ID");
            product.HomePageDisplayBlockID = SqlUtil<int>.GetValue(dr, "HomePageDisplayBlockID");
            product.TypeID = SqlUtil<int>.GetValue(dr, "TypeID");
            product.SupTypeID = SqlUtil<int>.GetValue(dr, "SupTypeID");
            product.UniqueString = SqlUtil<string>.GetValue(dr, "UniqueString");
            product.Title = SqlUtil<string>.GetValue(dr, "Title");
            product.MetaContent = SqlUtil<string>.GetValue(dr, "MetaContent");
            product.MetaDescription = SqlUtil<string>.GetValue(dr, "MetaDescription");
            product.Content = SqlUtil<string>.GetValue(dr, "Content");
            product.IsActive = SqlUtil<bool>.GetValue(dr, "IsActive");

            product.TitleEn = SqlUtil<string>.GetValue(dr, "Title_En");
            product.MetaContentEn = SqlUtil<string>.GetValue(dr, "MetaContent_En");
            product.MetaDescriptionEn = SqlUtil<string>.GetValue(dr, "MetaDescription_En");
            product.ContentEn = SqlUtil<string>.GetValue(dr, "Content_En");

            product.ProductDetail = SqlUtil<string>.GetValue(dr, "ProductDetail");
            product.DeliveryDetail = SqlUtil<string>.GetValue(dr, "DeliveryDetail");
            product.ImageSmallURL = SqlUtil<string>.GetValue(dr, "ImageSmallURL");
            product.ImageLargeURL = SqlUtil<string>.GetValue(dr, "ImageLargeURL");

            product.ProductDetailEn = SqlUtil<string>.GetValue(dr, "ProductDetail_En");
            product.ImageSmallURLEn = SqlUtil<string>.GetValue(dr, "ImageSmallURL_En");
            product.ImageLargeURLEn = SqlUtil<string>.GetValue(dr, "ImageLargeURL_En");

            product.CreatedAt = SqlUtil<DateTime>.GetValue(dr, "CreatedAt");
            product.CreatedBy = SqlUtil<string>.GetValue(dr, "CreatedBy");
            product.LastUpdatedAt = SqlUtil<DateTime>.GetValue(dr, "LastUpdatedAt");
            product.LastUpdatedBy = SqlUtil<string>.GetValue(dr, "LastUpdatedBy");
            product.Price = SqlUtil<string>.GetValue(dr, "Price");
            product.PriceEn = SqlUtil<string>.GetValue(dr, "Price_En");

            return product;
        }
    }
}
